﻿/**********************************************************************************
 * 程序说明：     数据表处理类
 * 创建日期：     2010.08.11
 * 程序制作：     agui
 * 联系方式：     mailto: 3549 90393@qq.com  
 * ********************************************************************************/
using System;
using System.Text;
using System.Data;
using System.Collections;
using System.Collections.Generic;
using PlugNT.Safe;
using PlugNT.Database.Common;
using PlugNT.Database.Common.Complex;

namespace PlugNT.Database.Common
{
    /// <summary>
    /// 数据表处理类
    /// </summary>
    public class Tables
    {
        private static string GetTableName(string tableName)
        {
            return StringHelper.SqlFilter(tableName);
        }

        /// <summary>
        /// String转换成Object类型
        /// </summary>
        /// <param name="type"></param>
        /// <param name="str"></param>
        /// <returns></returns>
        public static object ConvertToObject(Type type, string str)
        {
            switch (type.ToString())
            {
                case "System.Boolean":
                    return Convert.ToBoolean(str);
                case "System.Decimal":
                    return Convert.ToDecimal(str);
                case "System.Double":
                    return Convert.ToDouble(str);
                case "System.Single":
                    return Convert.ToSingle(str);
                case "System.Byte":
                    return Convert.ToByte(str);
                case "System.SByte":
                    return Convert.ToSByte(str);
                case "System.Int16":
                    return Convert.ToInt16(str);
                case "System.Int32":
                    return Convert.ToInt32(str);
                case "System.Int64":
                    return Convert.ToInt64(str);
                case "System.DateTime":
                    return Convert.ToDateTime(str);
                case "System.Char":
                    return Convert.ToChar(str);
                default:
                    return str;
            }
        }

        /// <summary>
        /// Object转换成String类型
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        private static string ConvertToString(object obj)
        {
            switch (obj.GetType().ToString())
            {
                case "System.Boolean":
                case "System.Decimal":
                case "System.Double":
                case "System.Single":
                case "System.Byte":
                case "System.SByte":
                case "System.Int16":
                case "System.Int32":
                case "System.Int64":
                    return obj.ToString();
                case "System.DateTime":
                    return "'" + obj.ToString() + "'";
                case "System.Char":
                case "System.String":
                    return "'" + StringHelper.SqlFilter(obj.ToString()) + "'";
                default:
                    return "'" + StringHelper.SqlFilter(obj.ToString()) + "'";
            }
        }

        #region 增修删
        /// <summary>
        /// 增加一条数据
        /// </summary>
        /// <param name="dbinfo">数据库连接信息类</param>
        /// <param name="tableName">表名称</param>
        /// <param name="dictions">字典类型字段和值</param>
        /// <returns></returns>
        public static bool Add(DbInfo dbinfo, string tableName, Dictionary<string, object> dictions)
        {
            bool flag = false;
            bool startflag = true;
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSqlValues = new StringBuilder();
            strSql.Append("insert into " + GetTableName(tableName) + " (");
            foreach (KeyValuePair<string, object> item in dictions)
            {
                if (startflag)
                {
                    strSql.Append("[" + item.Key + "]");
                    strSqlValues.Append(ConvertToString(item.Value));
                    startflag = false;
                }
                else
                {
                    strSql.Append(",[" + item.Key + "]");
                    strSqlValues.Append("," + ConvertToString(item.Value));
                }
            }
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append(strSqlValues.ToString());
            strSql.Append(")");

            //System.Web.HttpContext.Current.Response.Write(strSql.ToString() + "<br>");
            //System.Web.HttpContext.Current.Response.End();

            try
            {
                if (DbHelper.ExecuteSql(dbinfo,strSql.ToString()) > 0)
                {
                    flag = true;
                }
            }
            catch { }

            return flag;
        }

        /// <summary>
        /// 增加一条数据(默认配置)
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dictions"></param>
        /// <returns></returns>
        public static bool Add(string tableName, Dictionary<string, object> dictions)
        {
            return Add(GetDbInfo(), tableName, dictions);
        }



        /// <summary>
        /// 增加一条数据(重载)
        /// </summary>
        /// <param name="dbinfo">数据库连接信息类</param>
        /// <param name="tableName">表名称</param>
        /// <param name="hashtab">哈希类型字段和值</param>
        /// <returns></returns>
        public static bool Add(DbInfo dbinfo, string tableName, Hashtable hashtab)
        {
            bool flag = false;
            bool startflag = true;
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSqlValues = new StringBuilder();
            strSql.Append("insert into " + GetTableName(tableName) + " (");
            foreach (DictionaryEntry item in hashtab)
            {
                if (startflag)
                {
                    strSql.Append("[" + item.Key.ToString() + "]");
                    strSqlValues.Append(ConvertToString(item.Value));
                    startflag = false;
                }
                else
                {
                    strSql.Append(",[" + item.Key.ToString() + "]");
                    strSqlValues.Append("," + ConvertToString(item.Value));
                }
            }
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append(strSqlValues.ToString());
            strSql.Append(")");

            //System.Web.HttpContext.Current.Response.Write(strSql.ToString() + "<br>");
            //System.Web.HttpContext.Current.Response.End();

            try
            {
                if (DbHelper.ExecuteSql(dbinfo, strSql.ToString()) > 0)
                {
                    flag = true;
                }
            }
            catch { }

            return flag;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <param name="dbinfo">数据库连接信息类</param>
        /// <param name="tableName">表名称</param>
        /// <param name="dictions">字典类型字段和值</param>
        /// <param name="andWhere">条件</param>
        /// <returns></returns>
        public static bool Update(DbInfo dbinfo, string tableName, Dictionary<string, object> dictions, string andWhere)
        {
            bool flag = false;
            bool startflag = true;
            StringBuilder strSql = new StringBuilder();

            strSql.Append("update " + GetTableName(tableName) + " set ");

            foreach (KeyValuePair<string, object> item in dictions)
            {
                if (startflag)
                {
                    strSql.Append("[" + item.Key + "]=");
                    startflag = false;
                }
                else
                {
                    strSql.Append(",[" + item.Key + "]=");
                }
                strSql.Append(ConvertToString(item.Value));
            }
            strSql.Append(" where 1=1 " + andWhere);

            //System.Web.HttpContext.Current.Response.Write(strSql.ToString() + "<br>");
            //System.Web.HttpContext.Current.Response.End();

            try
            {
                if (DbHelper.ExecuteSql(dbinfo, strSql.ToString()) > 0)
                {
                    flag = true;
                }
            }
            catch { }
            return flag;
        }


        /// <summary>
        /// 更新一条数据(默认配置)
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dictions"></param>
        /// <param name="andWhere"></param>
        /// <returns></returns>
        public static bool Update(string tableName, Dictionary<string, object> dictions, string andWhere)
        {
            return Update(GetDbInfo(), tableName, dictions, andWhere);
        }


        /// <summary>
        /// 更新一条数据(重载)
        /// </summary>
        /// <param name="dbinfo">数据库连接信息类</param>
        /// <param name="tableName">表名称</param>
        /// <param name="hashtab">哈希类型字段和值</param>
        /// <param name="andWhere">条件</param>
        /// <returns></returns>
        public static bool Update(DbInfo dbinfo, string tableName, Hashtable hashtab, string andWhere)
        {
            bool flag = false;
            bool startflag = true;
            StringBuilder strSql = new StringBuilder();

            strSql.Append("update " + GetTableName(tableName) + " set ");

            foreach (DictionaryEntry item in hashtab)
            {
                if (startflag)
                {
                    strSql.Append("[" + item.Key.ToString() + "]=");
                    startflag = false;
                }
                else
                {
                    strSql.Append(",[" + item.Key.ToString() + "]=");
                }
                strSql.Append(ConvertToString(item.Value));
            }
            strSql.Append(" where 1=1 " + andWhere);

            //System.Web.HttpContext.Current.Response.Write(strSql.ToString() + "<br>");
            //System.Web.HttpContext.Current.Response.End();

            try
            {
                if (DbHelper.ExecuteSql(dbinfo, strSql.ToString()) > 0)
                {
                    flag = true;
                }
            }
            catch { }
            return flag;
        }


        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="dbinfo">数据库连接信息类</param>
        /// <param name="tableName">表名称</param>
        /// <param name="andWhere">条件</param>
        /// <returns></returns>
        public static int Delete(DbInfo dbinfo, string tableName, string andWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from [" + GetTableName(tableName) + "] ");
            strSql.Append(" where 1=1 " + andWhere);
            return DbHelper.ExecuteSql(dbinfo, strSql.ToString());
        }

        /// <summary>
        /// 删除一条数据(默认配置)
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="andWhere"></param>
        /// <returns></returns>
        public static int Delete( string tableName, string andWhere)
        {
            return Delete(GetDbInfo(),  tableName,  andWhere);
        }
        #endregion

        #region 查询
        /// <summary>
        /// 查询单行的单个列
        /// </summary>
        /// <param name="dbinfo">数据库连接信息类</param>
        /// <param name="tableName">表名称</param>
        /// <param name="columnName">列名称</param>
        /// <param name="andWhere">条件</param>
        /// <returns></returns>
        public static string GetObjectString(DbInfo dbinfo, string tableName, string columnName, string andWhere)
        {
            Object obj = DbHelper.GetSingle(dbinfo, string.Format("select top 1 [{0}] from [" + GetTableName(tableName) + "] where 1=1 {1}", columnName, andWhere));
            return obj == null ? "" : obj.ToString();
        }

        /// <summary>
        /// 查询单行的单个列(默认配置)
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columnName"></param>
        /// <param name="andWhere"></param>
        /// <returns></returns>
        public static string GetObjectString(string tableName, string columnName, string andWhere)
        {
            return GetObjectString(GetDbInfo(), tableName, columnName, andWhere);
        }





        /// <summary>
        /// 得到列表
        /// </summary>
        /// <param name="dbinfo">数据库连接信息类</param>
        /// <param name="tableName">表名称</param>
        /// <param name="topCount">前n条</param>
        /// <param name="andWhere">条件</param>
        /// <returns></returns>
        public static DataTable GetList(DbInfo dbinfo, string tableName,string columns, int topCount, string andWhere)
        {
            string strTopCount = (topCount > 0) ? " top " + topCount.ToString() : "";
            return DbHelper.GetDataTable(dbinfo, string.Format("select {0} {1} from [" + GetTableName(tableName) + "] where 1=1 {2}", strTopCount,columns, andWhere));
        }


        /// <summary>
        /// 得到列表(默认配置)
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="topCount"></param>
        /// <param name="andWhere"></param>
        /// <returns></returns>
        public static DataTable GetList(string tableName, string columns, int topCount, string andWhere)
        {
            return GetList(GetDbInfo(), tableName,columns, topCount, andWhere);
        }



        /// <summary>
        /// 得到列表(分页)
        /// </summary>
        /// <param name="dbinfo">数据库连接信息类</param>
        /// <param name="tableName">表名称</param>
        /// <param name="columns">列名称</param>
        /// <param name="pageSize">按n条分页</param>
        /// <param name="currPage">当前页数</param>
        /// <param name="andWhere">条件</param>
        /// <returns></returns>
        public static DataTable GetList(DbInfo dbinfo, string tableName,string columns, int pageSize, int currPage, string andWhere)
        {
            string sql = Sql.GetPagingString(pageSize, currPage, GetTableName(tableName), "id", columns, andWhere);
            return DbHelper.GetDataTable(dbinfo, sql);
        }


        /// <summary>
        ///  得到列表(分页-默认配置)
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="columns">列名称</param>
        /// <param name="pageSize">按n条分页</param>
        /// <param name="currPage">当前页数</param>
        /// <param name="andWhere">条件</param>
        /// <returns></returns>
        public static DataTable GetList(string tableName, string columns,int pageSize, int currPage, string andWhere)
        {
            return GetList(GetDbInfo(), tableName,columns, pageSize, currPage, andWhere);
        }

        /// <summary>
        /// 得到列表(分页)
        /// </summary>
        /// <param name="dbinfo">数据库连接信息类</param>
        /// <param name="tableName">表名称</param>
        /// <param name="columns">列名称</param>
        /// <param name="pageSize">按n条分页</param>
        /// <param name="currPage">当前页数</param>
        /// <param name="orderList">排序</param>
        /// <param name="andWhere">条件</param>
        /// <returns></returns>
        public static DataTable GetList(DbInfo dbinfo, string tableName, string columns,int pageSize, int currPage, string orderList, string andWhere)
        {
            string sql = Sql.GetPagingString(pageSize, currPage, GetTableName(tableName), "id", columns, orderList, andWhere);
            return DbHelper.GetDataTable(dbinfo, sql);
        }


        /// <summary>
        ///  得到列表(分页-默认配置)
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="columns">列名称</param>
        /// <param name="pageSize">按n条分页</param>
        /// <param name="currPage">当前页数</param>
        /// <param name="orderList">排序</param>
        /// <param name="andWhere">条件</param>
        /// <returns></returns>
        public static DataTable GetList(string tableName, string columns ,int pageSize, int currPage,  string orderList, string andWhere)
        {
            return GetList(GetDbInfo(), tableName, columns, pageSize, currPage, orderList, andWhere);
        }

        /// <summary>
        /// 得到列表(分页)
        /// </summary>
        /// <param name="dbinfo">数据库连接信息类</param>
        /// <param name="tableName">表名称</param>
        /// <param name="columns">列名称</param>
        /// <param name="pageSize">按n条分页</param>
        /// <param name="currPage">当前页数</param>
        /// <param name="sqlWhere">SQL条件</param>
        /// <param name="jinfo">JoinInfo</param>
        /// <returns></returns>
        public static DataTable GetList(DbInfo dbinfo, string tableName, string columns, int pageSize, int currPage, string andWhere, LeftJoinInfo jinfo)
        {
            string sql = Sql.GetPagingString(pageSize, currPage, GetTableName(tableName), "id", columns, andWhere, jinfo);
            return DbHelper.GetDataTable(dbinfo, sql);
        }


        /// <summary>
        ///  得到列表(分页-默认配置)
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="columns">列名称</param>
        /// <param name="pageSize">按n条分页</param>
        /// <param name="currPage">当前页数</param>
        /// <param name="sqlWhere">SQL条件</param>
        /// <param name="jinfo">JoinInfo</param>
        /// <returns></returns>
        public static DataTable GetList(string tableName, string columns, int pageSize, int currPage, string andWhere, LeftJoinInfo jinfo)
        {
            return GetList(GetDbInfo(), tableName, columns, pageSize, currPage, andWhere, jinfo);
        }


        /// <summary>
        /// 按条件查找的总数(分页)
        /// </summary>
        /// <param name="dbinfo">数据库连接信息类</param>
        /// <param name="tableName">表名称</param>
        /// <param name="andWhere">条件</param>
        /// <returns></returns>
        public static int GetCount(DbInfo dbinfo, string tableName, string andWhere)
        {
            string sql = "select count(1) from [" + GetTableName(tableName) + "] where 1=1 " + andWhere;
            return (int)DbHelper.GetSingle(dbinfo,sql);
        }

        /// <summary>
        /// 按条件查找的总数(分页-默认配置)
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="andWhere"></param>
        /// <returns></returns>
        public static int GetCount( string tableName, string andWhere)
        {
            return GetCount(GetDbInfo(), tableName, andWhere);
        }

        #endregion


        /// <summary>
        /// 返回重置列名的Sql语句
        /// </summary>
        /// <param name="dbinfo"></param>
        /// <param name="tabName"></param>
        /// <param name="idName"></param>
        /// <returns></returns>
        public static string GetReSetTableStr(DbInfo dbinfo, string tabName, string idName)
        {
            switch (dbinfo.DbType)
            {
                case "System.Data.SqlClient":
                    return string.Format("dbcc checkident({0},reseed,1)", StringHelper.SqlFilter(tabName));
                case "System.Data.OleDb":
                    return string.Format("alter table [{0}] alter column {1} counter(1,1)", StringHelper.SqlFilter(tabName), StringHelper.SqlFilter(idName));
                case "System.Data.OracleClient":
                    return string.Format("dbcc checkident({0},reseed,1)", StringHelper.SqlFilter(tabName));
                default:
                    return "";
            }
        }

        /// <summary>
        /// 返回重置列名的Sql语句(默认配置)
        /// </summary>
        /// <param name="tabName"></param>
        /// <param name="idName"></param>
        /// <returns></returns>
        public static string GetReSetTableStr(string tabName, string idName)
        {
            return GetReSetTableStr(GetDbInfo(), tabName, idName);
        }

        private static DbInfo GetDbInfo()
        {
            return PlugNT.Database.Common.Simple.DbHelper.dbinfo;

        }
    }
}



